﻿<cfsilent>
	<cfsetting requesttimeout="300" />
	<cfflush interval="1024" />
	<cfscript>
	
		temFile = GetTempDirectory() & createUUID() & ".xls";
		courseUpdate = 0;
		courseInsert = 0;

		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labImport", true);
		
		sequenceCourse = getProperty("serviceFactory").getBean("sequenceCourse");

	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入课程数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>
		
		
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="课程">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入课程信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>
				
				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />

				<cfif not listFindNoCase(rs.columnList, "课程号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "课程名称")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "任课单位")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "学分")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "排课周学时")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "总学时")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "理论学时")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "实践学时")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 课程号, 课程名称, 任课单位, 学分, 排课周学时, 总学时, 理论学时, 实践学时</p>
				</cfif>

				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p>
					
					<cftransaction>
				
						<cfloop query="rs">

							<!--- 导入数据 --->
							<cfset courseCode = rs["课程号"][rs.currentRow] />
							<cfset courseName = rs["课程名称"][rs.currentRow] />
							<cfset courseCredit = rs["学分"][rs.currentRow] />
							<cfset courseEnglish = "" />
							<cfset courseIntro = "" />
							<cfset courseReferenceBooks = "" />

							<cfset periodWeek = rs["排课周学时"][rs.currentRow] />
							<cfset period = rs["总学时"][rs.currentRow] />
							<cfset periodTheory = rs["理论学时"][rs.currentRow] />
							<cfset periodPractice = rs["实践学时"][rs.currentRow] />
							<cfset periodComputer = 0 />
							
							<cfset markMode = "0" />
							<cfset resitPolicy = "1" />
							<cfset abandon = "0" />

							<cfset instituteName = rs["任课单位"][rs.currentRow] />
							
							<!--- 可选数据 --->
							<cfif listFindNoCase(rs.columnList, "课程英文名称") AND len( rs["课程英文名称"][rs.currentRow] ) >
								<cfset courseEnglish = rs["课程英文名称"][rs.currentRow]/>
							</cfif>
							<cfif listFindNoCase(rs.columnList, "上机学时") AND isNumeric( rs["上机学时"][rs.currentRow] ) >
								<cfset periodComputer = rs["上机学时"][rs.currentRow] />
							</cfif>
							<cfif listFindNoCase(rs.columnList, "课程简介") AND len( rs["课程简介"][rs.currentRow] ) >
								<cfset courseIntro =  rs["课程简介"][rs.currentRow]/>
							</cfif>
							<cfif listFindNoCase(rs.columnList, "参考书目") AND len( rs["参考书目"][rs.currentRow] ) >
								<cfset courseReferenceBooks = rs["参考书目"][rs.currentRow] />
							</cfif>
							
							<cfif listFindNoCase(rs.columnList, "已停开") and ( rs["已停开"][rs.currentRow] eq "是" or rs["已停开"][rs.currentRow] eq "否" )>
								<cfif rs["已停开"][rs.currentRow] eq "是">
									<cfset abandon = "1" />
								<cfelse>
									<cfset abandon = "0" />
								</cfif>
							</cfif>
							
							<cfif listFindNoCase(rs.columnList, "可补考") and ( rs["可补考"][rs.currentRow] eq "是" or rs["可补考"][rs.currentRow] eq "否" )>
								<cfif rs["可补考"][rs.currentRow] eq "是">
									<cfset resitPolicy = "1" />
								<cfelse>
									<cfset resitPolicy = "0" />
								</cfif>
							</cfif>
							
							<!--- 第一步 课程类别代码 --->
							<cfset courseCataLogId = "00" />
							
							<cfif listFindNoCase(rs.columnList, "课程类别") and rs["课程类别"][rs.currentRow] neq "">
										
								<!---
									00	本校课程 (默认)
									S3	尔雅网络通识课
									S2	体育选项目
									S1	实验预约
									FF	外校课程
									B0	公共基础课
									B1	小语种外语
									B2	素质选修课
								--->
								
								<cfset courseCataLogName = rs["课程类别"][rs.currentRow] />
								
								<cfswitch expression="#courseCataLogName#">
									<cfcase value="本校课程"><cfset courseCataLogId = "00" /></cfcase>
									<cfcase value="尔雅网络通识课"><cfset courseCataLogId = "S3" /></cfcase>
									<cfcase value="体育选项目"><cfset courseCataLogId = "S2" /></cfcase>
									<cfcase value="实验预约"><cfset courseCataLogId = "S1" /></cfcase>
									<cfcase value="外校课程"><cfset courseCataLogId = "FF" /></cfcase>
									<cfcase value="公共基础课"><cfset courseCataLogId = "B0" /></cfcase>
									<cfcase value="小语种外语"><cfset courseCataLogId = "B1" /></cfcase>
									<cfcase value="素质选修课"><cfset courseCataLogId = "B2" /></cfcase>
								</cfswitch>
							
							</cfif>
							
							<!--- 第二步 检查各必填字段 --->
							<cfif len(courseCode) AND len(courseName) 
									AND isNumeric(courseCredit) 
									AND isNumeric(periodWeek) 
									AND isNumeric(periodTheory) AND isNumeric(periodPractice) AND isNumeric(period) >

									<!--- 第三步 检查任课单位 --->
									<cfset sql = "SELECT institute_id FROM t_institute WHERE institute_name = :instituteName " />
									<cfset queryObj = new Query( datasource=application.dnsMaster )/>
                            		<cfset queryObj.addParam( name="instituteName", value=instituteName, cfsqltype="cf_sql_varchar" )/>
                            		<cfset rs_institute = queryObj.execute( sql=sql ).getResult()/>
							
									<cfif rs_institute.recordCount eq 1>
									
											<cfset instituteId = rs_institute.institute_id />
								
											<!--- 
												任课单位检查无误 下一步检查课程号
											--->
									
											<cfset sql = "SELECT cid, course_english, intro, reference_books FROM t_course WHERE course_code = :courseCode " />
											<cfset queryObj = new Query( datasource=application.dnsMaster )/>
                        				    <cfset queryObj.addParam( name="courseCode", value=courseCode, cfsqltype="cf_sql_varchar" )/>
                            				<cfset rs_course = queryObj.execute( sql=sql ).getResult()/>
									
											<cfif rs_course.recordCount eq 1>

													<!--- 课程已存在 则更新课程 --->
													<cfset courseId = rs_course.cid />
													
													<!--- 数据安全覆盖 --->
													<cfif len(courseEnglish) eq 0 >
														<cfset courseEnglish = rs_course.course_english />
													</cfif>
													<cfif len(courseIntro) eq 0 >
														<cfset courseIntro = rs_course.intro />
													</cfif>
													<cfif len(courseReferenceBooks) eq 0 >
														<cfset courseReferenceBooks = rs_course.reference_books />
													</cfif>
													
													<cfset sql = "UPDATE t_course 
																	SET 
																		institute_id = :instituteId, 
																		course_name = :courseName, 
																		course_english = :courseEnglish, 
																		course_credit = :courseCredit, 
																		period_week = :periodWeek, 
																		period = :period, 
																		period_theory = :periodTheory, 
																		period_practice = :periodPractice, 
																		period_computer = :periodComputer, 
																		intro = :courseIntro, 
																		reference_books = :courseReferenceBooks, 
																		cat_id = :courseCataLogId 
																	WHERE 
																		cid = :courseId" />
													
													<cfset queryObj = new Query( datasource=application.dnsMaster )/>
                            						<cfset queryObj.addParam( name="courseId", value=courseId, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="instituteId", value=instituteId, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseName", value=courseName, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseEnglish", value=courseEnglish, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseCredit", value=courseCredit, cfsqltype="cf_sql_decimal", scale=2 )/>
                            						<cfset queryObj.addParam( name="periodWeek", value=periodWeek, cfsqltype="cf_sql_decimal", scale=1 )/>
                            						<cfset queryObj.addParam( name="period", value=period, cfsqltype="cf_sql_integer" )/>
                            						<cfset queryObj.addParam( name="periodTheory", value=periodTheory, cfsqltype="cf_sql_integer" )/>
                            						<cfset queryObj.addParam( name="periodPractice", value=periodPractice, cfsqltype="cf_sql_integer" )/>
                            						<cfset queryObj.addParam( name="periodComputer", value=periodComputer, cfsqltype="cf_sql_integer" )/>
                            						<cfset queryObj.addParam( name="courseIntro", value=courseIntro, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseReferenceBooks", value=courseReferenceBooks, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseCataLogId", value=courseCataLogId, cfsqltype="cf_sql_char" )/>
													
													<cfset queryObj.execute( sql=sql ) />
													
													<!--- 更新现有课程基础数据 --->
													<cfset courseUpdate ++ />

												<cfelse>

													<!--- 课程不存在 建立新课程 --->
													<cfset courseId = sequenceCourse.getID() />
													
													<cfset sql = "INSERT INTO t_course 
																	( 
																		cid, institute_id, 
																		course_name, course_english, course_credit, 
																		period_week, 
																		period, period_theory, period_practice, period_computer, 
																		intro, reference_books, 
																		cat_id, course_code, mark_mode, resit_policy, abandon 
																	) 
																	VALUES 
																	(
																		:courseId, :instituteId, 
																		:courseName, :courseEnglish, :courseCredit, 
																		:periodWeek, 
																		:period, :periodTheory, :periodPractice, :periodComputer, 
																		:courseIntro, :courseReferenceBooks, 
																		:courseCataLogId, :courseCode, :markMode, :resitPolicy, :abandon 
																	) " />

													<cfset queryObj = new Query( datasource=application.dnsMaster )/>
                            						<cfset queryObj.addParam( name="courseId", value=courseId, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="instituteId", value=instituteId, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseName", value=courseName, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseEnglish", value=courseEnglish, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseCredit", value=courseCredit, cfsqltype="cf_sql_decimal", scale=2 )/>
                            						<cfset queryObj.addParam( name="periodWeek", value=periodWeek, cfsqltype="cf_sql_decimal", scale=1 )/>
                            						<cfset queryObj.addParam( name="period", value=period, cfsqltype="cf_sql_integer" )/>
                            						<cfset queryObj.addParam( name="periodTheory", value=periodTheory, cfsqltype="cf_sql_integer" )/>
                            						<cfset queryObj.addParam( name="periodPractice", value=periodPractice, cfsqltype="cf_sql_integer" )/>
                            						<cfset queryObj.addParam( name="periodComputer", value=periodComputer, cfsqltype="cf_sql_integer" )/>
                            						<cfset queryObj.addParam( name="courseIntro", value=courseIntro, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseReferenceBooks", value=courseReferenceBooks, cfsqltype="cf_sql_varchar" )/>
                            						<cfset queryObj.addParam( name="courseCataLogId", value=courseCataLogId, cfsqltype="cf_sql_char" )/>
													<cfset queryObj.addParam( name="courseCode", value=courseCode, cfsqltype="cf_sql_varchar" )/>
													<cfset queryObj.addParam( name="markMode", value=markMode, cfsqltype="cf_sql_char" )/>
													<cfset queryObj.addParam( name="resitPolicy", value=resitPolicy, cfsqltype="cf_sql_char" )/>
													<cfset queryObj.addParam( name="abandon", value=abandon, cfsqltype="cf_sql_char" )/>
													
													<cfset queryObj.execute( sql=sql ) />
													
													<!--- 创建新课程 --->
													<cfset courseInsert ++ />

											</cfif>

										<cfelse>
											<p class="error">(<cfoutput>#courseName#</cfoutput>) 任课单位 (<cfoutput>#instituteName#</cfoutput>) 录入不正确. 该任课单位在系统中没有对应的数据记录.</p> 
									</cfif>
								
								<cfelse>
									<p class="error">(<cfoutput>#courseName#</cfoutput>) 任课单位 (<cfoutput>#instituteName#</cfoutput>) 学分、学时等相关信息有误. 课程信息未能录入</p> 
							</cfif>

						</cfloop>
						
					</cftransaction>
					

				</cfif>
			</cfif>
		</cfif>
		
		<p>新增课程 [<cfoutput>#courseInsert#</cfoutput>] 门, 更新现有课程 [<cfoutput>#courseUpdate#</cfoutput>] 门.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL('programCourse')#</cfoutput>">刷新课程</a>
		
		<!---<a target="_parent" href="<cfoutput>#buildURL('programCourse', targetArgs)#</cfoutput>">重新导入课程</a>--->
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>
